using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using MySql.Data.MySqlClient;
using FutureOpen.Core.func.util;

namespace FutureOpen.Core.func.sql
{
    public class GroupProvider
    {
        public static readonly int CREATER = 4;
        public static readonly int ADMIN = 3;
        public static readonly int APPLY = 1;
        public static readonly int USER = 2;

        public static readonly string ORDERBY_USR_COUNT = " group_person_count ";
        public static readonly string ORDERBY_ART_COUNT = " group_art_count ";
        public static readonly string ORDERBY_LASTUPDATE = " group_last_time ";
        public static readonly string ORDERBY_CREATETIME = " group_creat_time ";


        #region user create group

        public static int AddGroup(string title, string content, int usr, int cat)
        {
            return AddGroup(title, content, usr, 0, cat);
 
        }

        public static readonly string SQL_HAS_PIC = "select group_pic from grp where group_id={0}";
        public static readonly string SQL_UPDATE_PIC_ID = "update grp set group_pic={1}  where group_id={0}";
        public static int UpdatePic(int uid, byte[] bytes)
        {
            string sql = string.Format(SQL_HAS_PIC, uid);
            object obj = MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null);
            int pid = 0;
            if (obj != null && (pid = Convert.ToInt32(obj)) <= 0)
            {
              
                // insert picture and update the usr_pic from usr
                MySqlTransaction tran = null;
                try
                {
                    tran = MySqlHelper.GetTransaction();
                    pid = PicProvider.AddPic(tran, PicProvider.PIC_TYPE_ICON_USR, bytes, uid);
                    string updatesql = string.Format(SQL_UPDATE_PIC_ID, uid, pid);
                    MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), updatesql, null);
                                        

                    tran.Commit();
                }
                catch (Exception e)
                {
                    pid = -1;
                    tran.Rollback();
                }
                
            }
            else
            {
                PicProvider.UpdatePic(pid, string.Empty, bytes);
            }
            return pid;
        }

        public static readonly string SQL_ADD_GROUP = "insert into grp(group_name,group_desc,group_type,group_cat,group_create_time) Values('{0}','{1}',{2},{3},'{4}');select last_insert_id(); ";
        public static readonly string SQL_ADD_GROUP_USR = "INSERT INTO groupusr(groupusr_group, groupusr_usr,groupusr_type,groupusr_join_time, groupusr_last_time) VALUES({0},{1},{2},'{3}','{3}')";
        public static int AddGroup(string title, string content, int usr, int type,int cat)
        {
            title = StringUtil.CSTR(title);
            content = StringUtil.CSTR(content);
            string sql = string.Format(SQL_ADD_GROUP, title, content, type, cat, DateTime.Now);
            int id = Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
            if (id > 0)
            {
                sql = string.Format(SQL_ADD_GROUP_USR, id, usr, GroupProvider.CREATER, System.DateTime.Now);
                MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql, null);
                GroupProvider.ApplyGroup(id, usr, GroupProvider.ADMIN);
                CatProvider.INCREASE_CAT(cat); // add category
            }
            return id;
        }

                
        public static readonly string SQL_UPDATE_GROUP = "update grp set group_desc='{1}',group_cat={2},group_last_time='{3}' where group_id={0}";
        public static void UpdateGroup(int id, string content, int cat, int preCat)
        {
            content = StringUtil.CSTR(content);
            string sql = string.Format(SQL_UPDATE_GROUP, id, content, cat, DateTime.Now);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql, null);
            //maintain the group count 
            CatProvider.UPDATE_COUNT_CAT(preCat, cat);
        }

        

        /*
        public static readonly string SQL_ADD_GROUP_PIC = "insert into pic(pic_";
        public static readonly string SQL_UPDATE_GROUP_PIC = "update pic set pic_content=@pic where pic_id=@id";
        public static void UpdatePic(int groud_id, byte[] blob)
        {
            MySqlParameter param1 = new MySqlParameter("@pic",MySqlDbType.VarBinary, blob.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, blob);
            
            MySqlParameter param2 = new MySqlParameter("@id", groud_id);
            MySqlParameter[] pms = new MySqlParameter[] { param1, param2 };

            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), SQL_UPDATE_GROUP_PIC, pms);
        }
         */

        public static readonly string SQL_DEL_GROUP = "update grp set group_del=1 where group_id={0}";
        public static void DelGroup(int groupID)
        {
            string sql = string.Format(SQL_DEL_GROUP, groupID);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql, null);
        }
        #endregion


        #region user setting

        public static readonly string SQL_APPLY_GROUP_USR = "insert into groupusr(groupusr_group, groupusr_usr, groupusr_type) VALUES({0},{1},{2}); update grp set group_person_count=group_person_count+1 where group_id={0}";
        public static void ApplyGroup(int gid,int uid, int type)
        {
            string sql_apply_group = string.Format(SQL_APPLY_GROUP_USR, gid, uid, type);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql_apply_group, null);            
 
        }

        public static readonly string SQL_GET_USR_BY_TYPES = "select u.*,gu.* from usr u, groupusr gu where gu.groupusr_usr=u.usr_id and gu.groupusr_group={0} limit {1},{2}";
        public static DataSet GetGroupUsrByType(int gid, int start, int count)
        {
            string sql = string.Format(SQL_GET_USR_BY_TYPES, gid, start, start+count);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        public static readonly string SQL_GET_USR_BY_TYPES_COUNT = "select count(*) from usr u, groupusr gu where gu.groupusr_usr=u.usr_id and gu.groupusr_group={0}";
        public static int GetGroupUsrByTypeCount(int gid)
        {
            string sql = string.Format(SQL_GET_USR_BY_TYPES_COUNT, gid);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
        }

        public static readonly string SQL_QUIT_GROUP_USR = "delete from group_usr where groupusr_group={0} and groupusr_usr={1}; update grp set group_person_count=group_person_count-1 where group_id={0}";
        public static void QuitGroup(int gid, int uid)
        {
            string sql_quit_group = string.Format(SQL_QUIT_GROUP_USR, gid, uid);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql_quit_group, null); 
        }

        public static readonly string SQL_APPROVE_GROUP_USR = "update group_usr set groupusr_type={2} where groupusr_group={0} and groupusr_usr{1}";
        public static void UpdateGroupType(int gid, int uid, int type)
        {
            string sql_approve_group = string.Format(SQL_QUIT_GROUP_USR, gid, uid,type);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql_approve_group, null);
        }

        public static readonly string SQL_APPROVE_GROUP_USR_2 = "update groupusr set groupusr_type={1} where groupusr_id={0}";
        public static void UpdateGroupType(int id, int type)
        {
            string sql_approve_group = string.Format(SQL_APPROVE_GROUP_USR_2, id, type);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql_approve_group, null);
        }

        public static readonly string SQL_GET_USRTYPE = "select * from groupusr where groupusr_group={0} AND groupusr_usr={1}";
        public static bool IsAdmin(int gid, int uid)
        {
            string sql = string.Format(SQL_GET_USRTYPE, gid, uid);
            DataRow dr = MySqlHelper.ExecuteDatarow(MySqlHelper.CONNSTR(), sql, null);
            if (dr != null)
            {
                int type = Convert.ToInt16(dr["groupusr_type"]);
                if (type == CREATER || type == ADMIN)
                {
                    return true;
                }
            }
            return false;
 
        }

        public static readonly string SQL_GET_GROUP_IDS = "select groupusr_id from groupusr where groupusr_usr={0}";
        public static DataSet GetUsrGroupIDs(int uid)
        {
            string sql_my_group = string.Format(SQL_GET_GROUP_IDS, uid);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql_my_group, null);
        }
  
        public static readonly string SQL_GET_GROUP_INFO = "select a.* from grp a, groupusr b where a.group_id =b.groupusr_group and b.groupusr_usr={0} and b.groupusr_type>1 limit {1},{2}";
        public static DataSet GetUsrGroups(int uid, int start, int count)
        {
            string sql_my_group = string.Format(SQL_GET_GROUP_INFO, uid, start, start + count);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(),sql_my_group, null);             
        }

        public static readonly string SQL_GET_GROUP_INFO_COUNT = "select count(*) from grp a, groupusr b where a.group_id =b.groupusr_group and b.groupusr_usr={0} and b.groupusr_type > 1";
        public static int GetUsrGroupsCount(int uid)
        {
            string sql_my_group = string.Format(SQL_GET_GROUP_INFO_COUNT, uid);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql_my_group, null));
        }


        public static readonly string SQL_HAS_RIGHT = "select groupusr_type from groupusr where groupusr_group={0} AND groupusr_usr={1}";
        public static bool HasRight(int grp,int uid)
        {
            string sql_has_right = string.Format(SQL_HAS_RIGHT, grp, uid);
            Object obj = MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql_has_right);
            if (obj != null)
            {
                int type = Int16.Parse(obj.ToString());
                return type == GroupType.GROUPUSR_ADMIN || type == GroupType.GROUPUSR_OWNER;
            }
            return false;
        }
        #endregion

        
        public static readonly string SQL_GETGROUP_BYCAT = "select * from grp where group_cat={0} order by {3} limit {1},{2}";
        public static DataSet GetGroupByCat(int cat,int start, int end,string order)
        {
            string sql = string.Format(SQL_GETGROUP_BYCAT, cat, start, end, order);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null); 
        }

        public static readonly string SQL_GETGROUP_BYCAT_LIST = "select * from grp where group_cat in {0} order by {3} limit {1},{2}";
        public static DataSet GetGroupByCatList(int cat, int start, int end, string order)
        {
            
            string sql = string.Format(SQL_GETGROUP_BYCAT_LIST, cat, start, end, order);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        public static readonly string SQL_GETGROUP_BYCAT_COUNT = "select count(*) from grp where group_cat={0} ";
        public static int GetGroupByCatCount(int cat)
        {
            string sql = string.Format(SQL_GETGROUP_BYCAT_COUNT, cat);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
        }

       
        public static readonly string ORDER_USR_COUNT = "grp_person_count";
        public static readonly string ORDER_TOPIC_COUNT = "grp_art_count";
        public static readonly string ORDER_CREATE_TIME = "grp_create_time";
        public static readonly string ORDER_LAST_TIME = "grp_last_time";


        public static readonly string SQL_GET_USR_BY_GROUP = "select a.*,b.* from usr a, groupusr b where b.groupusr_group={0} and b.groupusr_usr=a.usr_id order by usr_last_time desc limit {1},{2}";
        public static DataSet GetGroupUsrByGroup(int grpid, int start, int end)
        {
            string sql = string.Format(SQL_GET_USR_BY_GROUP, grpid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        public static readonly string SQL_GET_USR_BY_GROUP_COUNT = "select count(*) from groupusr b where b.groupusr_group={0}";
        public static int GetGroupUsrByGroupCOUNT(int grpid)
        {
            string sql = string.Format(SQL_GET_USR_BY_GROUP_COUNT, grpid);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
        }


        public static readonly string SQL_GET_ADMIN_USR_BY_GROUP = "select a.* from usr a, groupusr b where b.groupusr_group={0} and b.groupusr_usr=a.usr_id and b.groupusr_type>1 order by usr_last_time desc";
        public static DataSet GetGroupAdminUsrByGroup(int grpid)
        {
            string sql = string.Format(SQL_GET_ADMIN_USR_BY_GROUP, grpid);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }


        public static readonly string SQL_GET_TOPIC_BY_GROUP = "select * from topic where topic_group={0} order by topic_id desc limit {1},{2}";
        public static DataSet GetTopicsByGroup(int grpid, int start, int end)
        {
            string sql = string.Format(SQL_GET_TOPIC_BY_GROUP, grpid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        public static readonly string SQL_GET_TOPIC_BY_GROUP_Count = "select count(*) from topic topic where topic_group={0}";
        public static int GetTopicsByGroupCount(int grpid)
        {
            string sql = string.Format(SQL_GET_TOPIC_BY_GROUP_Count, grpid);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
        }

        public static readonly string SQL_GET_GROUPUSR_BY_USR_GROUP = "select * from groupusr where groupusr_usr={0} and groupusr_group={1}";
        public static DataRow GetGroupUsrByGroup(int usr, int group)
        {
            string sql = string.Format(SQL_GET_GROUPUSR_BY_USR_GROUP, usr, group);
            return MySqlHelper.ExecuteDatarow(MySqlHelper.CONNSTR(), sql, null); 
        }


        public static readonly string SQL_GET_GROUP_BY_ID = "select * from grp where group_id={0}";
        public static DataRow GetGroupByID(int grpid)
        {
            string sql = string.Format(SQL_GET_GROUP_BY_ID, grpid);
            return MySqlHelper.ExecuteDatarow(MySqlHelper.CONNSTR(), sql, null); 
        }

        
        public static readonly string SQL_GET_RECENT_USRS = "select b.usr_id, b.usr_name, b.usr_pic from groupusr a, usr b where a.groupusr_group={0} and a.groupusr_usr=b.usr_id order by a.groupusr_last_time DESC limit {1},{2}";
        public static DataSet GetRecentUsrs(int gid, int start, int end)
        {
            string sql = string.Format(SQL_GET_RECENT_USRS, gid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql);
        }

        public static readonly string SQL_GET_HOT_USRS = "select b.usr_id, b.usr_name, b.usr_pic from groupusr a, usr b where a.groupusr_group={0} and a.groupusr_usr=b.usr_id order by a.groupusr_art_count DESC limit {1},{2}";
        public static DataSet GetHotUsers(int gid, int start, int end)
        {
            string sql = string.Format(SQL_GET_HOT_USRS, gid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql);
        }

        public static readonly string SQL_GET_RECENT_JOIN_USRS = "select b.usr_id, b.usr_name, b.usr_pic from groupusr a, usr b where a.groupusr_group={0} and a.groupusr_usr=b.usr_id order by a.groupusr_join_time DESC limit {1},{2}";
        public static DataSet GetRecentJoinUsrs(int gid, int start, int end)
        {
            string sql = string.Format(SQL_GET_RECENT_JOIN_USRS, gid, start, end);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql);
        }


       
        public static void AddTopic(int group, string title, string content,int usrid)
        {
            TopicProvider.AddTopic(title, content, usrid, group, TopicProvider.TOPIC_TYPE_NORMAL);
            string sql = string.Format(SQL_UPDATE_GROUP_TOPIC, group, DateTime.Now);
            IncreaseGroupArt(group, usrid);
        }

        public static void ReplyTopic(int group,int topicid, string content, int usrid)
        {
            TopicProvider.AddReply(content, topicid, usrid);
            IncreaseGroupArt(group, usrid);
        }

        public static readonly string SQL_UPDATE_GROUP_TOPIC = "update grp set group_last_time='{1}', group_art_count=group_art_count+1 where group_id={0}";
        private static readonly string SQL_UPDATE_ART_COUNT = "update groupusr set groupusr_art_count=groupusr_art_count+1 where groupusr_usr={0} and groupusr_group={1}";
        public static void IncreaseGroupArt(int groupid,int uid)
        {
            string sql = string.Format(SQL_UPDATE_GROUP_TOPIC, groupid, DateTime.Now);            
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql);
            sql = string.Format(SQL_UPDATE_ART_COUNT, uid, groupid);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql);
        }


        public static readonly string SQL_GETGROUP = "select * from grp order by {2} limit {0},{1}";
       public static DataSet GetGroup(int start, int end, string p)
        {
            string sql = string.Format(SQL_GETGROUP,  start, end, p);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }



        public static readonly string SQL_GETGROUP_COUNT = "select count(*) from grp";
        public static int GetGroupCount()
        {
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), SQL_GETGROUP_COUNT, null));
        }

        public static readonly string SQL_DELETE_GROUP_USR = "delete from groupusr where groupusr_id={0}; update grp set group_person_count=group_person_count-1 where group_id={1}; ";
        public static void DeleteGroupUsr(int key,int gid)
        {
            string sql = string.Format(SQL_DELETE_GROUP_USR, key, gid);
            MySqlHelper.ExecuteNonQuery(MySqlHelper.CONNSTR(), sql, null);
        }

        public static readonly string SQL_GET_TOPIC_BY_ORDER = "select * from topic where topic_group={0} and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(topic_create_time) order by {1} desc limit {2},{3}";
        public static DataSet GetTopicsByGroupOrder(int gid, string p, int start, int count)
        {
            string sql = string.Format(SQL_GET_TOPIC_BY_ORDER, gid, p, start, start+count);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        #region search function
        public static readonly string SQL_SEARCH_GROUP_COUNT = "select count(*) from grp where group_name like '%{0}%'";
        public static int SearchGroupByLikeCount(string p)
        {
            string sql = string.Format(SQL_SEARCH_GROUP_COUNT, p);
            return Convert.ToInt32(MySqlHelper.ExecuteScalar(MySqlHelper.CONNSTR(), sql, null));
           
        }

        public static readonly string SQL_SEARCH_GROUP = "select * from grp where group_name like '%{0}%' order by group_last_time DESC limit {1},{2}";
        public static DataSet SearchGroupByLike(string p, int start, int count)
        {
            string sql = string.Format(SQL_SEARCH_GROUP, p, start, start + count);
            return MySqlHelper.ExecuteDataset(MySqlHelper.CONNSTR(), sql, null);
        }

        #endregion
    }
}
